package exercise;

import util.DataSourceUtils;

import javax.xml.crypto.Data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Demo12 {
    public static void main(String[] args) throws SQLException {
//        find("经理");
//        find1("张三");
//        delete("王七");
//        find2();
//        updata(1,10000);
//        find3(8800);
        find4("市场部");

    }
    public static void find(String empJoin) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "SELECT *FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id` WHERE emp.`emp_join`=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,empJoin);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String empName = rs.getString("emp_name");
            String empSex = rs.getString("emp_sex");
            String empJoin1 = rs.getString("emp_join");
            String empSalary = rs.getString("emp_salary");
            String empDate = rs.getString("emp_date");
            String deptId = rs.getString("dept_id");
            String depName = rs.getString("dep_name");
            System.out.println(id+","+empName+","+empSex+","+empJoin1+","+empSalary+","+empDate+","+depName);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
    public static void find1(String name) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();
        String sql = "SELECT *FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id` WHERE emp.`emp_name`=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String empName = rs.getString("emp_name");
            String empSex = rs.getString("emp_sex");
            String empJoin1 = rs.getString("emp_join");
            String empSalary = rs.getString("emp_salary");
            String empDate = rs.getString("emp_date");
            String deptId = rs.getString("dept_id");
            System.out.println(id+","+empName+","+empSex+","+empJoin1+","+empSalary+","+empDate+","+deptId);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
    public static void delete(String name) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "DELETE FROM emp WHERE emp_name=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,name);
        int row = pstmt.executeUpdate();
        System.out.println(row);
        pstmt.close();
        conn.close();
    }
    public static void find2() throws SQLException {
        Connection conn = DataSourceUtils.getConnection();


        String sql = "SELECT * FROM emp WHERE emp_name LIKE '张%';";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
            String empName = rs.getString("emp_name");
            String empSalary = rs.getString("emp_salary");
            System.out.println(empName+"="+empSalary);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
    public static void updata(int id,double empSalary) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "UPDATE emp SET emp_salary=? WHERE id=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setDouble(1,empSalary);
        pstmt.setInt(2,id);
        int row = pstmt.executeUpdate();
        System.out.println(row);
        pstmt.close();
        conn.close();

    }
    public static void find3(double empSalary1) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();


        String sql = "SELECT *FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id` WHERE emp.`emp_salary`>=? ;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setDouble(1,empSalary1);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Employee> list = new ArrayList<>();
        while(rs.next()){
            int id = rs.getInt("id");
            String empName = rs.getString("emp_name");
            String empSex = rs.getString("emp_sex");
            String empJoin1 = rs.getString("emp_join");
            Double empSalary = rs.getDouble("emp_salary");
            String empDate = rs.getString("emp_date");
            String deptId = rs.getString("dept_id");
            String depName = rs.getString("dep_name");
            Employee employee = new Employee(id, empName, empSex, empJoin1, empSalary, empDate, depName);
            list.add(employee);
        }
        for (Employee e : list) {
            System.out.println(e);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
    public static void find4(String deptName1) throws SQLException {
        Connection conn = DataSourceUtils.getConnection();

        String sql = "SELECT *FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id` WHERE dept.`dep_name`=?;";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1,deptName1);
        ResultSet rs = pstmt.executeQuery();
        ArrayList<Employee> list = new ArrayList<>();
        while(rs.next()){
            int id = rs.getInt("id");
            String empName = rs.getString("emp_name");
            String empSex = rs.getString("emp_sex");
            String empJoin1 = rs.getString("emp_join");
            Double empSalary = rs.getDouble("emp_salary");
            String empDate = rs.getString("emp_date");
            String deptId = rs.getString("dept_id");
            String depName = rs.getString("dep_name");
            Employee employee = new Employee(id, empName, empSex, empJoin1, empSalary, empDate, depName);
            list.add(employee);
        }
        for (Employee e : list) {
            System.out.println(e);
        }
        rs.close();
        pstmt.close();
        conn.close();
    }





}
